package com.sfy.les.excel;

import java.util.HashMap;
import java.util.List;

import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFHyperlink;
import org.apache.poi.ss.usermodel.Font;

import com.sfy.les.table.TableInfo;

public class ExportUtils {
	
	/**白色样式*/
	public static short whiteColor = HSSFColor.HSSFColorPredefined.WHITE.getIndex();
	/**黑色样式*/
	public static short blackColor = HSSFColor.HSSFColorPredefined.BLACK.getIndex();
	/**红色样式*/
	public static short redColor = HSSFColor.HSSFColorPredefined.RED.getIndex();
	/**灰色样式*/
	public static short greyColor = HSSFColor.HSSFColorPredefined.GREY_40_PERCENT.getIndex();
	/**浅褐色样式*/
	public static short tanColor = HSSFColor.HSSFColorPredefined.TAN.getIndex();
	/**蓝色样式*/
	public static short blueColor = HSSFColor.HSSFColorPredefined.BLUE.getIndex();
	/**玫瑰色样式*/
	public static short roseColor = HSSFColor.HSSFColorPredefined.ROSE.getIndex();
	/**深海绿色样式*/
	public static short greenColor = HSSFColor.HSSFColorPredefined.SEA_GREEN.getIndex();
	/**浅绿色样式*/
	public static short aquaColor = HSSFColor.HSSFColorPredefined.AQUA.getIndex();
	/**居中样式*/
	public static HorizontalAlignment alginCenter = HorizontalAlignment.CENTER;
	/**左对齐样式*/
	public static HorizontalAlignment alginLeft = HorizontalAlignment.LEFT;
	/**右对齐样式*/
	public static HorizontalAlignment alginRight = HorizontalAlignment.RIGHT;
	/***标题样式*/
	public static CellStyle titleStyle;
	/***表单头部样式*/
	public static CellStyle tableTitleStyle;
	/**表单头部褐色底色居中样式*/
	public static CellStyle tableInfoTanCenterStyle;
	/**表总量统计样式*/
	public static CellStyle tableCountStyle;
	/**表超链接样式*/
	public static CellStyle tableLinkStyle;
	/**表单头部白色底色样式*/
	public static CellStyle tableInfoWhiteStyle;
	/**表单头部玫瑰色色底色样式*/
	public static CellStyle tableInfoRoseStyle;
	/**返回主页样式*/
	public static CellStyle backHomeStyle;
	/**表单头部褐色底色居左样式*/
	public static CellStyle tableInfoTanLeftStyle;
	/**表单头部褐色底色居左样式*/
	public static CellStyle tableInfoGreenLeftStyle;
	/**表表单头部Aqua色底色居中样式*/
	public static CellStyle tableInfoAquaCenterStyle;
	
	/**
	 * 主页要显示的信息
	 * @return
	 */
	public static String[] getDataBaseInfoArr(){
		String[] dataBaseInfoArr = {
				"所有者"
				,"数据库版本"
				,"IP地址"
				,"端口号"
				,"DB用户"
				,"DB密码"
		};
		
		return dataBaseInfoArr;
	}
	
	/**
	 * 表要显示的信息
	 * @return
	 */
	public static String[] getTableInfoArr(){
		String[] tableInfoArr = {
				"字段名"
				,"类型"
				,"长度"
				,"小数点"
				,"允许空值"
				,"是否主键"
				,"是否自增长"
				,"默认值"
				,"注释"	
		};
		
		return tableInfoArr;
	}

	/**
	 * 添加单元格
	 * @param wb HSSFWorkbook对象
	 * @param sheet sheet对象
	 * @param row 行对象
	 * @param i 第几个单元格
	 * @param value 单元格的值
	 * @param style 单元格的样式
	 * @param dateRange 合并单元格的Range对象
	 */
	public static void addCell(SXSSFWorkbook wb, SXSSFSheet sheet, SXSSFRow row, int i, String value, CellStyle style, CellRangeAddress dateRange){
		SXSSFCell titelCell = row.createCell(i);
		titelCell.setCellValue(value);
		titelCell.setCellStyle(style);
		if(dateRange != null){
			setRegionStyle(sheet,dateRange,style);
		}
	}
	
	/**
	 * 设置合并单元格后的样式
	 * @param sheet
	 * @param dateRange
	 * @param cs
	 */
	public static void setRegionStyle(SXSSFSheet sheet, CellRangeAddress dateRange,CellStyle cs) {
		for (int i = dateRange.getFirstRow(); i <= dateRange.getLastRow(); i++) {
            SXSSFRow row = sheet.getRow(i);
            for (int j = dateRange.getFirstColumn(); j <= dateRange.getLastColumn(); j++) {
                SXSSFCell cell = row.getCell(j)==null?row.createCell(j):row.getCell(j);
                cell.setCellStyle(cs);
            }
        }
    }
	
	/**
	 * 新增超链接行
	 * @param tableINfo 表信息
	 * @param tmpRow 当前行
	 * @param thisSheet 当前页
	 * @param wb XSSFWorkbook对象
	 */
	public static void addHyperLink(TableInfo tableInfo,int tmpRow, SXSSFSheet thisSheet, SXSSFWorkbook wb, String sheetName){
		//创建新的一行，存放数据库的表名和注释信息
		SXSSFRow tableInfoRow = thisSheet.createRow((short)tmpRow);
		CellRangeAddress dateRange3 = new CellRangeAddress(tmpRow, tmpRow, 1, 2);//起始行,结束行,起始列,结束列
		CellRangeAddress dateRange4 = new CellRangeAddress(tmpRow, tmpRow, 3, 4);//起始行,结束行,起始列,结束列
		thisSheet.addMergedRegion(dateRange3);
		thisSheet.addMergedRegion(dateRange4);
		addHyperCell(wb, thisSheet, tableInfoRow, 1, tableInfo, tableLinkStyle, dateRange3, sheetName);
		addCell(wb, thisSheet, tableInfoRow, 3, tableInfo.getTableDesc(), tableInfoWhiteStyle, dateRange4);
				
	}
	
	/**
	 * 添加超链接单元格
	 * @param wb HSSFWorkbook对象
	 * @param sheet sheet对象
	 * @param row 行对象
	 * @param i 第几个单元格
	 * @param value 单元格的值
	 * @param style 单元格的样式
	 * @param range 合并单元格的Range对象
	 */
	public static void addHyperCell(SXSSFWorkbook wb, SXSSFSheet sheet, SXSSFRow row, int i, TableInfo tableInfo, CellStyle style, CellRangeAddress range, String sheetName){
		 /* 连接跳转*/  
		CreationHelper createHelper = wb.getCreationHelper();
        SXSSFCell lineCell = row.createCell(i);        
        XSSFHyperlink  hyperlink = (XSSFHyperlink) createHelper.createHyperlink(HyperlinkType.URL);
        // "#"表示本文档    "明细页面"表示sheet页名称  "A10"表示第几列第几行
        if("返回首页".equals(sheetName)){
        	 hyperlink.setAddress("#数据结构清单!A1");  
             lineCell.setHyperlink(hyperlink);  
        }else{
        	 hyperlink.setAddress("#"+sheetName+"!A1");  
             lineCell.setHyperlink(hyperlink);  
        }
        // 点击进行跳转  
        lineCell.setCellValue(tableInfo.getTableName());  
        lineCell.setCellStyle(style);
        if(range != null){
			setRegionStyle(sheet,range,style);
		}
	}
	
	/**
	 * 获取sheet页名称
	 * @param tableInfo
	 * @return
	 */
	private static String getSheetName(TableInfo tableInfo, HashMap<String,Object> tableIndex){
		String sheetName = tableInfo.getTableName();
		if(sheetName.length() >= 31){
			sheetName = sheetName.substring(28).replace("-", "_");//sheet页名字大于31，自动截取前31位字符
		}
		int index = tableIndex.get(sheetName)==null?0:Integer.parseInt(tableIndex.get(sheetName).toString());
        if(index == 0){
        	tableIndex.put(sheetName, "1");
        }else{
        	tableIndex.put(sheetName, (index + 1) + "");
        	sheetName = sheetName + "_" + (index + 1);
        }
		return sheetName;
	}
	
	/**
	 * 创建单元格样式
	 * @param book
	 */
	public static void cresteCellStyle(SXSSFWorkbook wb){
		titleStyle = getCellStyle(wb,(short) 20,redColor,true,whiteColor,false,alginCenter);
		tableTitleStyle = getCellStyle(wb,(short) 16,blackColor,false,greyColor,false,alginCenter);
		tableInfoTanCenterStyle = getCellStyle(wb,(short) 14,blackColor,false,tanColor,false,alginCenter);
		tableInfoTanLeftStyle = getCellStyle(wb,(short) 14,blackColor,false,tanColor,false,alginLeft);
		tableCountStyle = getCellStyle(wb,(short) 15,redColor,false,whiteColor,false,alginCenter);
		tableLinkStyle = getCellStyle(wb,(short) 15,blueColor,false,whiteColor,true,alginCenter);
		tableInfoWhiteStyle = getCellStyle(wb,(short) 15,blackColor,false,whiteColor,false,alginCenter);
		tableInfoRoseStyle = getCellStyle(wb,(short) 18,blackColor,false,roseColor,false,alginCenter);
		backHomeStyle = getCellStyle(wb,(short) 13,whiteColor,true,blueColor,false,alginCenter);
		tableInfoGreenLeftStyle = getCellStyle(wb,(short) 14,blackColor,false,greenColor,false,alginLeft);
		tableInfoAquaCenterStyle = getCellStyle(wb,(short) 14,blackColor,false,aquaColor,false,alginCenter);
	}
	
	/**
	 * 设置单元格样式
	 * @param book HSSFWorkbook对象
	 * @param fontSize 字体大小
	 * @param fontCloloIndex 字体颜色
	 * @param weightFlag 是否加粗
	 * @param backGroundcloloIndex 背景颜色
	 * @param underLine 是否加下划线
	 * @param align 居中/左/右
	 * @return
	 */
	public static CellStyle getCellStyle(SXSSFWorkbook book,short fontSize,short fontCloloIndex,boolean weightFlag,short backGroundcloloIndex,boolean underLine,HorizontalAlignment align){
		// 颜色设置为填充，不水平居中
		CellStyle style = book.createCellStyle();
		Font font = book.createFont();
		font.setFontName("微软雅黑");
		font.setFontHeightInPoints(fontSize);// 设置字体大小
		font.setColor(fontCloloIndex);
		if(weightFlag){
			font.setBold(true);//字体增粗
		}
		if(underLine){
			font.setUnderline((byte) 1);
		}
		
		style.setFont(font);
		style.setAlignment(align); // 居中/左/右
		style.setBorderBottom(BorderStyle.MEDIUM); //下边框
		style.setBorderLeft(BorderStyle.MEDIUM);//左边框
		style.setBorderTop(BorderStyle.MEDIUM);//上边框
		style.setBorderRight(BorderStyle.MEDIUM);//右边框
		style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		style.setFillForegroundColor(backGroundcloloIndex);
		style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直
		style.setWrapText(true); // 自动换行设置
		return style;
	}
	
	/**
	 * 获取表名称与sheet页名称的对应关系
	 * @return
	 */
	public static HashMap<String, String> getTableNameSheetNameMap(List<TableInfo> tableInfoList){
		HashMap<String, String> tableNameSheetNameMap = new HashMap<String, String>();
		HashMap<String,Object> tableIndex = new HashMap<String,Object>();
		for (TableInfo tableInfo : tableInfoList) {
			String tableName = tableInfo.getTableName();
			tableNameSheetNameMap.put(tableName, getSheetName(tableInfo, tableIndex));
		}
		return tableNameSheetNameMap;
	}
	
}
